package servidor.roteamento;


import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;

import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import org.xml.sax.SAXException;

import comum.Localizacao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;



public class GrafoLoaderFromDB {

    private static final int MAXIMO_RESTRICOES = 3;

    private static final String CRUZAMENTO = "cruzamento";
    private static final String FINAL = "final";
    private static final String PONTE = "ponte";
    private static final String SUBMAPA = "submapa";

    private static final String TAG_VERTICES = "vertices";

	public static final String DATABASE_URL = "jdbc:mysql://127.0.0.1:3306/wai2008";
	public static final String DATABASE_USER = "root";
	public static final String DATABASE_PASSWORD = "letmein";    
    
	private Connection connection = null;
	
    /**
     * Alterado por Ricardo Bertolazo
     */
    public GrafoLoaderFromDB() throws SQLException {
    	String information = null;
    	int info = 0;    	
		
		PreparedStatement stmt = null;
		ResultSet rs = null;    	
    	
        try {        	        	
            //TODO: passar para carregar grafos
        	
			connection = this.getConnection();		
        	
		} catch (SQLException sqle) {
			throw sqle;
		}
		
		/*
		 * colocar no fim da conexão
		 */
/*
 			rs.close();
			stmt.close();
			connection.close();
 */
    }
    
	private Connection getConnection() throws SQLException {
		return DriverManager.getConnection(
				DATABASE_URL, DATABASE_USER, DATABASE_PASSWORD);
	}    


	public ResultSet getGraphsFromDB(){
		ResultSet rs = null;
		try{			
			PreparedStatement stmt = connection.prepareStatement("SELECT vertexId, name, level FROM wai2008.Vertex where type = 'submapa'");
			rs = stmt.executeQuery();
		}
		catch (SQLException e){			
		}
		
		return rs;		
	}
	
	public ResultSet getGraphChildNodesFromDB(int parentId){
		ResultSet rs = null;
		try{			
			PreparedStatement stmt = connection.prepareStatement("SELECT name, type, x, y, floor andar FROM wai2008.Vertex where parent = " + parentId);
			rs = stmt.executeQuery();
		}
		catch (SQLException e){			
		}
		
		return rs;		
	}
	
	public String getVertexName(int vertexId){
		ResultSet rs = null;
		String vertexName = "";
		try{			
			PreparedStatement stmt = connection.prepareStatement("SELECT name FROM wai2008.Vertex where vertexId = " + vertexId);
			rs = stmt.executeQuery();
			rs.next();
			vertexName = rs.getString(1);
		}
		catch (SQLException e){			
		}	
		
		return vertexName;		
	}	
	
	/*
	 * This function returns the Edges of the graph specified by the parentId
	 */
	public ResultSet getEdgesFromDB(int parentId){
		ResultSet rs = null;
		try{			
			PreparedStatement stmt = connection.prepareStatement("SELECT * FROM wai2008.Edge Where VertexFrom IN " +
					                            "(SELECT vertexId from wai2008.Vertex where parent =" + parentId + ")");
			rs = stmt.executeQuery();
		}
		catch (SQLException e){			
		}
		
		return rs;		
	}
	
    public Collection load() {

        Collection grafoHierarquico = new ArrayList();

        
//*************************************************************************        
        
        int vertexId = 0; 
        int level = 0;
        String name = "";
        
        
        
        try{
	        //coletando SUBMAPAS
        	ResultSet graphsResult = this.getGraphsFromDB();
			while (graphsResult.next()) {
				vertexId = graphsResult.getInt(1);
				name = graphsResult.getString(2);
				level = graphsResult.getInt(3);															
	      				
	            Grafo grafo = new Grafo(name, level);	
	                        	        
	            //coletando VÉRTICES de cada submapa
	            ResultSet childNodesResult =  getGraphChildNodesFromDB(vertexId);	            
	    		while (childNodesResult.next()) {
	    			String childName = "";
	    			String childType = "";
	    			int x = 0;
	    			int y = 0;
	    			int andar = 0; 
	    				    			
					childName = childNodesResult.getString(1);
					childType = childNodesResult.getString(2);
					x = Integer.parseInt(childNodesResult.getString(3));
					y = Integer.parseInt(childNodesResult.getString(4)); 
					andar = childNodesResult.getInt(5); 
	        
	                Vertice vertice = null;
	                if (childType.equalsIgnoreCase(CRUZAMENTO)) {
	                    vertice = new VerticeCruzamento();
	                } else if (childType.equalsIgnoreCase(FINAL)) {
	                    vertice = new VerticeFinal();
	                } else if (childType.equalsIgnoreCase(PONTE)) {
	                    vertice = new VerticePonte();
	                } else if (childType.equalsIgnoreCase(SUBMAPA)) {
	                    vertice = new VerticeSubMapa();
	                } else {
	                    // TODO ERRO!!! Tipo de vértice inválido!
	                	System.out.println("ERRO");
	                }
	                
	                vertice.setNome(childName);
	                Localizacao localizacao = new Localizacao(x, y, andar);
	                vertice.setLocalizacao(localizacao);
	                vertice.setGrafoQueContem(grafo);
	                grafo.addVertice(vertice);	    				                	                	                	                	                	                	               	                               
	            }
	    		
				ResultSet edgesResult =  this.getEdgesFromDB(vertexId);
	    		while (edgesResult.next()) {
	    			int vertexFromId = 0;
	    			int vertexToId = 0;
	    			int[] costs = new int[3];
	    			
	    			vertexFromId = edgesResult.getInt(2);
	    			vertexToId = edgesResult.getInt(3);
	    			costs[0] = edgesResult.getInt(4);
	    			costs[1] = edgesResult.getInt(5); 
	    			costs[2] = edgesResult.getInt(6);    			    			
	    		
	    			String vertexFrom = this.getVertexName(vertexFromId);
	    			String vertexTo = 	this.getVertexName(vertexToId);
	    				    			
                    Vertice vOrigem = grafo.getVertice(vertexFrom);
                    Vertice vDestino = grafo.getVertice(vertexTo);
                    
                    VerticeAdjacente adjacente = new VerticeAdjacente(vDestino, costs);
                    vOrigem.addVerticeAdjacente(adjacente);
	    		}
	    		
	    		grafoHierarquico.add(grafo);
	        }
			
			//popular e testar com o mapa inteiro!
			//*arestas: são carregadas por Grafo
			    			
        	}
        	catch(SQLException e){
        	}        	       

        return grafoHierarquico;


    }
}